package cz.acies.genea.data.sync;

import java.io.File;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbutils.QueryRunner;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.genea.data.dao.GedFamilyDAO;
import cz.acies.genea.data.dao.GedPersonBindingDAO;
import cz.acies.genea.data.dao.GedPersonDAO;
import cz.acies.genea.data.gedcom.model.ChildRef;
import cz.acies.genea.data.gedcom.model.EventFact;
import cz.acies.genea.data.gedcom.model.Family;
import cz.acies.genea.data.gedcom.model.Gedcom;
import cz.acies.genea.data.gedcom.model.Name;
import cz.acies.genea.data.gedcom.model.ParentFamilyRef;
import cz.acies.genea.data.gedcom.model.Person;
import cz.acies.genea.data.gedcom.model.SpouseFamilyRef;
import cz.acies.genea.data.gedcom.model.SpouseRef;
import cz.acies.genea.data.gedcom.parser.ModelParser;
import cz.acies.genea.data.persist.GedFamily;
import cz.acies.genea.data.persist.GedPerson;
import cz.acies.genea.data.persist.GedPersonBinding;
import cz.acies.genea.data.persist.GedProject;
import cz.acies.genea.utils.GED;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UpdateQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.functions.SQLFunc;
import cz.acies.utils.DateUtils;
import cz.acies.utils.StringUtils;
import cz.acies.utils.UIDGenerator;

public class GedcomImport
{
	//private static String projectFileName = "project.rodx";
	
	public static void main(String[] args) {
		try {
			String filePath = "";
			
			// HOME
			String jdbcUrl = "jdbc:mysql://localhost:3306/gedcom";
			String jdbcDriver = "org.gjt.mm.mysql.Driver";
			
			Properties props = new Properties();
			props.setProperty("user", "root");
			props.setProperty("password", "smart");
			props.setProperty("useUnicode", "true");
			props.setProperty("characterEncoding", "UTF8");
			props.setProperty("characterSetResults", "UTF8");
			/*
			// WORK
			String jdbcUrl = "jdbc:mysql://localhost:3305/gedcom";
			String jdbcDriver = "org.gjt.mm.mysql.Driver";

			Properties props = new Properties();
			props.setProperty("user", "root");
			props.setProperty("password", "");
			props.setProperty("useUnicode", "true");
			props.setProperty("characterEncoding", "UTF8");
			props.setProperty("characterSetResults", "UTF8");
			*/
			String dir = "d:/java/repository/acies/genea/UR.M.000000000000001";
			String fn = "Dytrych.Jaroslav.ged";
			String path = dir+"/"+fn;
			File file = new File(""+path);
			
			String dialectClass = "cz.acies.uniql.dialect.MySQLDialect"; 
			
			DBConnector connector = DBConnector.instance();
			connector.setUrl(jdbcUrl);
			connector.setDriver(jdbcDriver);
			connector.setDialectClass(dialectClass);
			connector.setProps(props);
			connector.initialize();

			
			GedcomImport inst = new GedcomImport();
			inst.setProjectId("GE.P.000000000000001");
			inst.setSourceFile(file);
			inst.sync();
			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	private static List<String> months = Arrays.asList(new String[] { "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC" });

	private File srcfile;
	private String sourceFileId;
	private String projectId;

	private Map<String, String> syncIdRegGC;
	private Map<String, String> syncIdRegGE;

	protected QueryRunner qr;

	
	public GedcomImport() {
	}
	
	public void sync() {
		try {
			GedFamilyDAO fDao = new GedFamilyDAO();
			GedPersonDAO pDao = new GedPersonDAO();

			GedProject pjt = new GedProject(this.getProjectId(), null);
			
			ModelParser modelParser = new ModelParser();
			Gedcom gedcom = modelParser.parseGedcom(this.getSourceFile());

			syncIdRegGC = dbSyncData();
			syncIdRegGE = MapUtils.invertMap(syncIdRegGC);

			Map<String, GedFamily> families = new LinkedHashMap<String, GedFamily>(); 
			Map<String, GedPerson> people = new LinkedHashMap<String, GedPerson>(); 
			
			for (Family f : gedcom.getFamilies()) {
				//System.out.println(f.getId() + " " + f.getHusbandRefs());
				String xref = f.getId();
				String status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";

				Map<String, Object> params = new HashMap<String, Object>();
				params.put("status", "'"+status+"'");

				GedFamily family = new GedFamily();
				if (status.equals("U")) {
					String id = ""+syncIdRegGC.get(xref);
					updateSyncData(id, xref, params);
					family.setId(id);
					family.setProjectId(getProjectId());
					if (fDao.existsRecord(id)) {
					} else {
						fDao.insert(family);
					}
				} else {
					String id = UIDGenerator.createUID("GE.F");
					family.setId(id);
					family.setProjectId(getProjectId());
					params.put("status", "'S'");
					insertSyncData(id, xref, params);
					fDao.insert(family);
				}
				families.put(""+xref, family);

				if (!f.getHusbandRefs().isEmpty()) {
					SpouseRef spose = f.getHusbandRefs().get(0);
					xref = spose.getRef();
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					String id  = "";
					GedPerson person = new GedPerson();
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId());
							id = pDao.insert(person);
						} 
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId());
						id = pDao.insert(person);
					}
					family.setMan(person);
					fDao.update(family);
					
					people.put(""+xref, person);
				}
				if (!f.getWifeRefs().isEmpty()) {
					SpouseRef spose = f.getWifeRefs().get(0);
					xref = spose.getRef();
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					String id  = "";
					GedPerson person = new GedPerson();
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId());
							id = pDao.insert(person);
						}
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId());
						id = pDao.insert(person);
					}
					family.setWoman(person);
					fDao.update(family);
					
					people.put(""+xref, person);
				}
				//System.out.println(f.getChildRefs());
				for (ChildRef i : f.getChildRefs()) {
					xref = i.getRef();
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					
					String id = "";
					GedPerson person = new GedPerson();
					
					params = new HashMap<String, Object>();
					params.put("status", "'"+status+"'");
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId()); 
							id = pDao.insert(person);
						}
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId()); 
						id = pDao.insert(person);
					}
					people.put(""+xref, person);
					family.addChild(person);
					
				}
			}
			
			for (Person p : gedcom.getPeople()) {
				String xref = p.getId();
				String status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
				String id = "";
				
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("status", "'"+status+"'");
				if (status.equals("U")) {
					id = ""+syncIdRegGC.get(xref);
					params.put("status", "S");
					updateSyncData(id, xref, params);
				} else {
					id = UIDGenerator.createUID("GE.R");
					params.put("status", "'N'");
					insertSyncData(id, xref, params);
				}
				
				GedPerson person = new GedPerson();
				if (people.containsKey(xref)) {
					person = people.get(xref);
				}
				person.setProjectId(projectId);

				Map<String, Object> row = new HashMap<String, Object>();
				
				row.put("id", p.getId());
				List<Name> names = p.getNames();
				for (Name name : names) {
					String displayName = name.getDisplayValue();
					
					int pos = displayName.indexOf("/");
					String firstName = (pos > 0) ? displayName.substring(0, pos) : "";
					String maidenName = (pos > 0) ? displayName.substring(pos).replace("/", "") : "";
					String lastName = name.getMarriedName();
					if (lastName == null || lastName.length() == 0) lastName = maidenName;
					
					row.put("firstName", firstName);
					row.put("lastName", lastName);
					row.put("maidenName", maidenName);
					
					person.setFirstName(firstName);
					person.setLastName(lastName);
					person.setMaidenName(maidenName);
					
					break;
				}
				List<EventFact> efs = p.getEventsFacts();
				for (EventFact ef : efs) {
					String key = ef.getTag();
					if (key.equals("SEX")) {
						row.put("gender", ef.getValue());
						person.setGender(ef.getValue());
					} else if (key.equals("BIRT")) {
						row.put("birthDate", ef.getDate());
						person.setBirthDate(parseDate(ef.getDate()));
						row.put("birthPlace", ef.getPlace());
						person.setBirthPlace(ef.getPlace());
					} else if (key.equals("DEAT")) {
						row.put("deathDate", ef.getDate());
						person.setDeathDate(parseDate(ef.getDate()));
						row.put("deathPlace", ef.getPlace());
						person.setDeathPlace(ef.getPlace());
					}
				}
				if (p.getParentFamilyRefs() != null && !p.getParentFamilyRefs().isEmpty()) {
					List<ParentFamilyRef> parents = p.getParentFamilyRefs();
					for (ParentFamilyRef pf : parents) {
						xref = "" + pf.getRef();
						GedFamily family = families.get(xref);
						person.setParents(family);
					}
				}
				if (!pDao.recordExists(id)) {
					id = pDao.insert(person);
				} else {
					pDao.update(person);
				}
			}
			
			GedPersonBindingDAO bDao = new GedPersonBindingDAO(); 
			for (Person p : gedcom.getPeople()) {
				String xref = p.getId();
				if (syncIdRegGC.containsKey(xref)) {
					String id = syncIdRegGC.get(xref);
					if (p.getParentFamilyRefs() != null && !p.getParentFamilyRefs().isEmpty()) {
						String bindingType = "" + GED.Type.PARENT;
						List<ParentFamilyRef> parents = p.getParentFamilyRefs();
						for (ParentFamilyRef pf : parents) {
							xref = "" + pf.getRef();
							GedFamily family = families.get(xref);
							
							if (family.hasId()) {
								String connectId = family.getId();
								GedPersonBinding bind = new GedPersonBinding(null, id, connectId, bindingType, "" + GED.Extend.FAMILY);
								if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
									bDao.insert(bind);
								}
							}
							if (family.hasMan() && family.getMan().hasId()) {
								String connectId = family.getMan().getId();
								GedPersonBinding bind = new GedPersonBinding(null, id, connectId, bindingType, "" + GED.Extend.FATHER);
								if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
									bDao.insert(bind);
								}
							}
							if (family.hasWoman() && family.getWoman().hasId()) {
								String connectId = family.getWoman().getId();
								GedPersonBinding bind = new GedPersonBinding(null, id, connectId, bindingType, "" + GED.Extend.MOTHER);
								if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
									bDao.insert(bind);
								}
							}
						}
					}
					
					if (p.getSpouseFamilyRefs() != null && !p.getSpouseFamilyRefs().isEmpty()) {
						//String gender = person.getGender();
						List<SpouseFamilyRef> partners = p.getSpouseFamilyRefs();
						for (SpouseFamilyRef partner : partners) {
							xref = partner.getRef();
							if (syncIdRegGC.containsKey(xref)) {
								GedFamily family = families.get(xref);
								String bindingType = "" + GED.Type.PARTNER;
								if (family.hasMan() && family.hasWoman()) {
									String extend = "" + GED.Extend.PERSON;
									GedPerson man = family.getMan();
									GedPerson woman = family.getWoman();
									GedPersonBinding bind = new GedPersonBinding();
									if (man.hasId() && woman.hasId()) {
										String objectId = family.getMan().getId();
										String connectId = family.getWoman().getId();
										bind = new GedPersonBinding(null, objectId, connectId, bindingType, extend);
										if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
											bDao.insert(bind);
										}
										objectId = family.getWoman().getId();
										connectId = family.getMan().getId();
										bind = new GedPersonBinding(null, objectId, connectId, bindingType, extend);
										if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
											bDao.insert(bind);
										}								
									}
								}
								if (family.hasId()) {
									String objectId = id;
									String connectId = family.getId();
									GedPersonBinding bind = new GedPersonBinding(null, objectId, connectId, "" + GED.Type.PARTNER, "" + GED.Extend.FAMILY);
									if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
										bDao.insert(bind);
									}
								}
								bindingType = "" + GED.Type.CHILD;
								Set<String> set = family.getChildren().keySet();
								for (String connectId : set) {
									String extend = "" + GED.Extend.PERSON;
									String objectId = id;
									GedPersonBinding bind = new GedPersonBinding(null, objectId, connectId, bindingType, extend);
									if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
										bDao.insert(bind);
									}
									if (family.hasId()) {
										extend = "" + GED.Extend.FAMILY;
										objectId = family.getId();
										bind = new GedPersonBinding(null, objectId, connectId, bindingType, extend);
										if (!bDao.existsRecord(bind.getObjectId(), bind.getConnectId(), bind.getBindingType(), bind.getExtend())) {
											bDao.insert(bind);
										}										
									}
								}
							}
						}
					}
				}
			}
			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	private java.util.Date parseDate(String str) throws Exception {
		java.util.Date date = java.sql.Date.valueOf("2099-01-01");
		if (str != null) {
			String[] as = str.split(" ");
			String ds = "";
			if (as.length >= 3) {
				String d = as[0];
				String m = as[1];
				String y = as[2];
				int mm = months.indexOf(m);
				//System.out.println("m: " + m + ", mm" + mm);
				if (mm < 0 || mm > 11) { 
					y = "2099"; mm = 0; d = "1"; 
				}
				ds = y + "-" + StringUtils.leftPad(""+(mm+1), 2, "0")+"-"+ StringUtils.leftPad(d, 2, "0");
				date = DateUtils.parseDate(ds, "yyyy-MM-dd");
			}
		}
		return date;
	}

	// -----  -----------------------------------------------------------------
	
	protected void insertSyncData( String id, String xref, Map<String, Object> params) throws Exception {	
		params.put("id", "'"+xref+"'");
		params.put("connectId", "'"+id+"'");
		params.put("datelastUpdate", new SQLFunc("NOW"));
		
		InsertQuery sql = new InsertQuery(dbmd())
		.addFrom("sync_gedcom")
		.addParams(params);
		qr().update(sql.toSQLString());
		
		syncIdRegGC.put(""+xref, ""+id);
		syncIdRegGE.put(""+id, ""+xref);
	}
	
	protected void updateSyncData( String id, String xref, Map<String, Object> params) throws Exception {
		qr().update(new UpdateQuery(dbmd())
			.addFrom("sync_gedcom")
			.addParam("id", ""+xref)
			.addParam("connectId", ""+id)
			.addParam("status", "S")
			.addParam("datelastUpdate", new SQLFunc("NOW"))
			.add(new UniQLCondition("sync_gedcom.id", "=", "'"+xref+"'"))
			.toSQLString()
		);
	}

	// -----  -----------------------------------------------------------------
	
	protected Map<String, String> dbSyncData() {
		Map<String, String> data = new HashMap<String, String>();
		try {
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			SelectQuery sql = new SelectQuery(DBConnector.instance().dbmd()).addFrom("sync_gedcom");
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> row : list) {
				String id = ""+row.get("id");
				String connectId = ""+row.get("connectId");
				data.put(id, connectId);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return data;
	}

	// -----  -----------------------------------------------------------------

	protected QueryRunner qr() {
		if (qr == null) { qr = new QueryRunner(ds()); }
		return qr;
	}
	
	protected DBMetaData dbmd() { return DBConnector.instance().dbmd(); }
	
	protected DataSource ds() {	return DBConnector.instance().getDataSource();	}

	// -----  -----------------------------------------------------------------
	
	public File getSourceFile() throws Exception {
		return srcfile;
	}
	
	public void setSourceFile(File file) {
		this.srcfile = file;
	}

	public String getSourceFileId() {
		return sourceFileId;
	}

	public void setSourceFileId(String sourceFileId) {
		this.sourceFileId = sourceFileId;
	}

	public String getProjectId() {
		return projectId;
	}

	public void setProjectId(String projectId) {
		this.projectId = projectId;
	}

}
